﻿//======================================================================
//
//        Copyright (C) 苏州逸仙软件    
//        All rights reserved
//
//        filename : D_T_MemberInfo_Exp.cs
//        description :
//
//        created by 周济 at 2010/12/14 9:24:18
//        mail:13912699233@139.com
//
//======================================================================


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace huaguwang
{
    public class D_T_Member_Exp : D_T_Member
    {

        //        @begin int =0,
        //@page int =10,
        //@userid int=0,
        //@nr nvarchar(200)=''
        public DataTable FindActivity(int begin, int page, int id, string key)
        {
            var ds = MSSQLHelper.SQLConType.C1.Exp_RunProcedure("P_GetUserOperate", "ds",
                new System.Data.SqlClient.SqlParameter("@begin", SqlDbType.Int) { Value = begin },
                new System.Data.SqlClient.SqlParameter("@page", SqlDbType.Int) { Value = page },
                new System.Data.SqlClient.SqlParameter("@userid", SqlDbType.Int) { Value = id },
                new System.Data.SqlClient.SqlParameter("@nr", SqlDbType.NVarChar) { Value = key });
            return ds.Tables[0];
        }
        public int FindActivity(int id, string key)
        {
            int i = 0;
            var ds = MSSQLHelper.SQLConType.C1.Exp_RunProcedure("P_GetUserOperate",
                out i,
                new System.Data.SqlClient.SqlParameter("@userid", SqlDbType.Int) { Value = id },
                new System.Data.SqlClient.SqlParameter("@nr", SqlDbType.NVarChar) { Value = key });
            return i;
        }


        /// <summary>
        /// 用户密码加密解密的密钥
        /// </summary>
        public const string MemBerKey = "89123567";
        /// <summary>
        /// 登录的时候用来进行操作
        /// </summary>
        /// <param name="strName">登录名</param>
        /// <param name="strPassWord">登录密码</param>
        /// <returns></returns>
        public M_T_Member MemBerLogin(string strName, string strPassWord)
        {
            StringBuilder strB = new StringBuilder();
            strB.Append(" SELECT     id, Name, Sex, Birthday, Gold, Grade, FreezeType, FreezeDate, Flag, PassWord, Tel, Email, ");
            strB.Append(" OpFreezeType, OpFreezeDate, isApprove, IDCard, WinStock, ProblemA,  ");
            strB.Append(" ProblemB, AnswerA, AnswerB ");
            strB.Append(" FROM         dbo.T_Member ");
            strB.Append(" WHERE     (Name = @name) AND (PassWord = @pass) AND (Flag = 1) ");

            var model = MSSQLHelper.SQLConType.C1.Exp_GetModel<M_T_Member>(strB.ToString(),
                base.DataRowToModel,
                new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.NVarChar) { Value = strName },
                new System.Data.SqlClient.SqlParameter("@pass", System.Data.SqlDbType.NVarChar) { Value = strPassWord }
                );
            return model;
        }
        /// <summary>
        /// 登录的时候用来进行操作
        /// </summary>
        /// <param name="id">登录名</param>
        /// <param name="strPassWord">登录密码</param>
        /// <returns></returns>
        public M_T_Member MemBerLogin(int id, string strPassWord)
        {
            StringBuilder strB = new StringBuilder();
            strB.Append(" SELECT     id, Name, Sex, Birthday, Gold, Grade, FreezeType, FreezeDate, Flag, PassWord, Tel, Email, ");
            strB.Append(" OpFreezeType, OpFreezeDate, isApprove, IDCard, WinStock, ProblemA,  ");
            strB.Append(" ProblemB, AnswerA, AnswerB ");
            strB.Append(" FROM         dbo.T_Member ");
            strB.Append(" WHERE     (id = @name) AND (PassWord = @pass) AND (Flag = 1) ");

            var model = MSSQLHelper.SQLConType.C1.Exp_GetModel<M_T_Member>(strB.ToString(),
                base.DataRowToModel,
                new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.Int) { Value = id },
                new System.Data.SqlClient.SqlParameter("@pass", System.Data.SqlDbType.NVarChar) { Value = strPassWord }
                );
            return model;
        }
        /// <summary>
        /// 判断是否存在相同的登录名
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IsURL(string loginName, int id)
        {
            var isname = MSSQLHelper.SQLConType.C1.Exp_ExistName<string>(
                "T_Member", id, "Name", loginName, System.Data.SqlDbType.NVarChar, "");
            return isname;
        }
        /// <summary>
        /// 判断用户及输入的身份证号码是否正确
        /// <remarks>
        /// 此方法用在找回密码的时候使用
        /// </remarks>
        /// </summary>
        /// <param name="strName"></param>
        /// <param name="strIDCard"></param>
        /// <returns></returns>
        public bool IsIDCard(string strName, string strIDCard)
        {
            StringBuilder strB = new StringBuilder();
            strB.Append(" SELECT     count(id) as c ");
            strB.Append(" FROM         dbo.T_Member ");
            strB.Append(" WHERE     (Name = @name) AND (IDCard = @IDCard) AND (Flag = 1) ");

            var isid = MSSQLHelper.SQLConType.C1.Exp_Exists(strB.ToString(),
                 new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.NVarChar) { Value = strName },
                 new System.Data.SqlClient.SqlParameter("@IDCard", System.Data.SqlDbType.NVarChar) { Value = strIDCard });
            return isid;
        }
        /// <summary>
        /// 把dr 转换成 问题答案类
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private M_T_Member_Exp.ProblemAnswer DRToProblemAnswer(DataRow dr)
        {
            var model = new M_T_Member_Exp.ProblemAnswer();
            model.AnswerA = dr["AnswerA"].Exp_Trim();
            model.AnswerB = dr["AnswerB"].Exp_Trim();
            model.ProblemA = dr["ProblemA"].Exp_Trim();
            model.ProblemB = dr["ProblemB"].Exp_Trim();
            return model;
        }
        /// <summary>
        /// 根据登录名,获取相关的问题和答案
        /// </summary>
        /// <param name="strName"></param>
        /// <returns></returns>
        public M_T_Member_Exp.ProblemAnswer GetProblem(string strName)
        {
            StringBuilder strB = new StringBuilder();
            strB.Append(" SELECT     ProblemA,  ");
            strB.Append(" ProblemB, AnswerA, AnswerB ");
            strB.Append(" FROM         dbo.T_Member ");
            strB.Append(" WHERE     (Name = @name)  AND (Flag = 1) ");
            var DS = MSSQLHelper.SQLConType.C1.Exp_GetModel<M_T_Member_Exp.ProblemAnswer>(
                strB.ToString(),
                DRToProblemAnswer,
                new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.NVarChar) { Value = strName });
            return DS;
        }
        /// <summary>
        /// 重置相关用户的密码
        /// </summary>
        /// <param name="strName"></param>
        /// <param name="strPassWord"></param>
        /// <returns></returns>
        public int ResetPassWord(string strName, string strPassWord)
        {
            StringBuilder strB = new StringBuilder();
            strB.Append(" update T_Member ");
            strB.Append(" set password=@P ");
            strB.Append(" where name=@N and flag=1 ");
            var refint = MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strB.ToString(),
                new System.Data.SqlClient.SqlParameter("@N", System.Data.SqlDbType.NVarChar) { Value = strName },
                new System.Data.SqlClient.SqlParameter("@P", System.Data.SqlDbType.NVarChar) { Value = strPassWord });

            return refint;
        }
        /// <summary>
        /// 重置相关用户的密码
        /// </summary>
        /// <param name="strName"></param>
        /// <param name="strPassWord"></param>
        /// <returns></returns>
        public int ResetPassWord(int id, string strPassWordOLD)
        {
            strPassWordOLD = strPassWordOLD.Exp_Encrypt(MemBerKey);
            StringBuilder strB = new StringBuilder();
            strB.Append(" update T_Member ");
            strB.Append(" set password=@P ");
            strB.Append(" where id=@N and flag=1 ");
            var refint = MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strB.ToString(),
                new System.Data.SqlClient.SqlParameter("@N", System.Data.SqlDbType.Int) { Value = id },
                new System.Data.SqlClient.SqlParameter("@P", System.Data.SqlDbType.NVarChar) { Value = strPassWordOLD });

            return refint.Exp_IsGreaterThan(0, id);
        }
        /// <summary>
        /// 重置相关用户的密码
        /// </summary>
        /// <param name="strName"></param>
        /// <param name="strPassWord"></param>
        /// <returns></returns>
        public int UpdateIsApprove(int id, bool isApprove)
        {
            StringBuilder strB = new StringBuilder();
            strB.Append(" update T_Member ");
            strB.Append(" set isApprove=@P ");
            strB.Append(" where id=@N and flag=1 ");
            var refint = MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strB.ToString(),
                new System.Data.SqlClient.SqlParameter("@N", System.Data.SqlDbType.Int) { Value = id },
                new System.Data.SqlClient.SqlParameter("@P", System.Data.SqlDbType.Bit) { Value = isApprove });
            return refint.Exp_IsGreaterThan(0, id);
        }
        /// <summary>
        /// 激活相关用户帐户
        /// </summary>
        /// <param name="strName"></param>
        /// <returns></returns>
        public int Activation(string strName)
        {
            StringBuilder strB = new StringBuilder();
            strB.Append(" update T_Member ");
            strB.Append(" set isApprove=1 ");
            strB.Append(" where name=@N and flag=1 ");
            var refint = MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strB.ToString(),
                new System.Data.SqlClient.SqlParameter("@N", System.Data.SqlDbType.NVarChar) { Value = strName });

            return refint;
        }
        /// <summary>
        /// 冻结用户帐户
        /// </summary>
        /// <param name="strName">用户名</param>
        /// <param name="IsLoginFreeze">true:冻结登录 false:冻结操作</param>
        /// <param name="e">冻结类型,请注意,不要传值为正常的类型</param>
        /// <param name="day">冻结天数,如果是永远冻结则自动进行99999天的冻结</param>
        /// <returns></returns>
        public int Freeze(string strName, bool IsLoginFreeze, M_T_Member_Exp.Freeze_Type e, int day)
        {
            string strSql = "";
            DateTime dt = DateTime.Now;
            //计算冻结时间
            switch(e)
            {
                case M_T_Member_Exp.Freeze_Type.freeze:
                    dt = dt.AddDays(day);
                    break;
                case M_T_Member_Exp.Freeze_Type.foreverfreeze:
                    dt = dt.AddDays(99999);
                    break;
                default:
                    throw new Exception("无法进行正常状态的冻结");
            }
            //判断冻结类型
            if(IsLoginFreeze)
            {
                strSql = "FreezeType={0}, FreezeDate='{1}'".
                    Exp_Format((int)e, dt.Exp_GetString(ExpandDateTime.DateFormType.EnDate));
            }
            else
            {
                strSql = "OpFreezeType={0}, OpFreezeDate='{1}'".
                    Exp_Format((int)e, dt.Exp_GetString(ExpandDateTime.DateFormType.EnDate));
            }
            //执行更新
            StringBuilder strB = new StringBuilder();
            strB.Append(" update T_Member ");
            strB.Append(" set  ");
            strB.Append(strSql);
            strB.Append(" where name=@N and flag=1 ");
            var refint = MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strB.ToString(),
                new System.Data.SqlClient.SqlParameter("@N", System.Data.SqlDbType.NVarChar) { Value = strName });

            return refint;
        }
        /// <summary>
        /// 解除用户帐户冻结状态,永久冻结的不可以解除
        /// </summary>
        /// <param name="strName">用户名</param>
        /// <param name="IsLoginFreeze">true:冻结登录 false:冻结操作</param>
        /// <returns></returns>
        public int FreezeThaw(string strName, bool IsLoginFreeze)
        {
            string strSql = "";
            var intnormal = (int)M_T_Member_Exp.Freeze_Type.normal;
            var intforeverfreeze = (int)M_T_Member_Exp.Freeze_Type.foreverfreeze;
            //判断冻结类型
            if(IsLoginFreeze)
            {
                strSql = "FreezeType";
            }
            else
            {
                strSql = "OpFreezeType";
            }
            //执行更新
            StringBuilder strB = new StringBuilder();
            strB.Append(" update T_Member ");
            strB.Append(" set  ");
            strB.Append(strSql);
            strB.Append("=");
            strB.Append(intnormal);
            strB.Append(" where name=@N and flag=1 and ");
            strB.Append(strSql);
            strB.Append("<>");
            strB.Append(intforeverfreeze);
            var refint = MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strB.ToString(),
                new System.Data.SqlClient.SqlParameter("@N", System.Data.SqlDbType.NVarChar) { Value = strName });

            return refint;
        }

        /// <summary>
        /// 后台管理中的列表查询sql基础语句
        /// </summary>
        /// <returns></returns>
        public StringBuilder GetFindSQL()
        {
            StringBuilder strB = new StringBuilder();
            strB.Append(" SELECT     id, Name, CASE WHEN 0 < len(ProblemA + ProblemB) THEN '有' ELSE '无' END AS tw,  ");
            strB.Append(" OpFreezeType, FreezeType, OpFreezeDate, FreezeDate , WinStock,  ");
            strB.Append(" isApprove, Gold, Grade, IDCard ");
            strB.Append(" FROM         dbo.T_Member ");
            strB.Append(" WHERE     (Flag = 1) ");
            return strB;
        }
        public M_T_Member_Exp.FindModel DRToFindModel(DataRow dr)
        {

            //string strSel = " id, Name, CASE WHEN 0 < len(ProblemA + ProblemB) THEN '有' ELSE '无' END AS tw,   " +
            //              "OpFreezeType, FreezeType,OpFreezeDate=CONVERT (nvarchar(10), OpFreezeDate,120) , " +
            //              "FreezeDate=CONVERT (nvarchar(10), FreezeDate,120) , WinStock,   isApprove, Gold, Grade, IDCard ";

            var model = new M_T_Member_Exp.FindModel();
            model.id = dr["id"].Exp_IntTryParse();
            model.搏股胜率 = dr["WinStock"].Exp_Trim() + "%";
            model.操作冻结 = GetFreezeType(dr["OpFreezeType"]);
            model.操作冻结时间 = dr["OpFreezeDate"].Exp_Trim();
            model.登录冻结 = GetFreezeType(dr["FreezeType"]);
            model.登录冻结时间 = dr["FreezeDate"].Exp_Trim();
            model.积分 = dr["Grade"].Exp_Trim();
            model.金币 = dr["Gold"].Exp_DecimalTryParse().Exp_Money();
            model.身份证 = dr["IDCard"].Exp_Trim();
            model.问题设置否 = dr["tw"].Exp_Trim();
            model.姓名 = dr["Name"].Exp_Trim();
            model.验证否 = dr["isApprove"].Exp_Trim();
            return model;
        }
        public string GetFreezeType(object intEindex)
        {
            var i = 0;
            if(!int.TryParse(intEindex.ToString(), out i))
            {
                i = -1;
            }
            try
            {
                var e = (M_T_Member_Exp.Freeze_Type)i;
                return GetFreezeType(e);
            }
            catch
            {
                return "参数错误";
            }

        }
        public string GetFreezeType(M_T_Member_Exp.Freeze_Type e)
        {
            string str = "";
            switch(e)
            {
                case M_T_Member_Exp.Freeze_Type.normal:
                    str = "正常";
                    break;
                case M_T_Member_Exp.Freeze_Type.freeze:
                    str = "冻结";
                    break;
                case M_T_Member_Exp.Freeze_Type.foreverfreeze:
                    str = "永久";
                    break;
                default:
                    str = "参数错误";
                    break;
            }
            return str;
        }
        /// <summary>
        /// 后台管理的分页查询
        /// </summary>
        /// <param name="begin"></param>
        /// <param name="page"></param>
        /// <param name="userName">用户名</param>
        /// <param name="isYZ">是否验证</param>
        /// <param name="loginDJType">-1 所有,登录冻结类型</param>
        /// <param name="OPDJTZ">-1 所有 操作冻结类型</param>
        /// <returns></returns>
        public List<M_T_Member_Exp.FindModel> FindList(int begin, int page, string userName, bool? isYZ, int loginDJType, int OPDJTZ)
        {
            var strB = new StringBuilder();
            strB.Append(" and Name like '%'+@name+'%' ");
            if(isYZ != null)
            {
                strB.Append(" and isApprove=" + (isYZ.Value ? "1" : "0"));
            }
            if(loginDJType >= 0)
            {
                strB.Append(" and FreezeType=" + loginDJType);
            }
            if(OPDJTZ >= 0)
            {
                strB.Append(" and OpFreezeType=" + OPDJTZ);
            }

            string strSel = " id, Name, CASE WHEN 0 < len(ProblemA + ProblemB) THEN '有' ELSE '无' END AS tw,   " +
                "OpFreezeType, FreezeType,OpFreezeDate=CONVERT (nvarchar(10), OpFreezeDate,120) , " +
                "FreezeDate=CONVERT (nvarchar(10), FreezeDate,120) , WinStock,   isApprove, Gold, Grade, IDCard ";
            string strWhere = " flag=1 " + strB.ToString();
            var strPage = ExpandMSSQL.GetPageSQL(
                    begin,
                    page,
                    strSel,
                    " T_Member ",
                    " id ",
                    strWhere,
                    " id desc "
                    );

            return MSSQLHelper.SQLConType.C1.Exp_GetList<M_T_Member_Exp.FindModel>(strPage,
                DRToFindModel,
                new System.Data.SqlClient.SqlParameter("@name", SqlDbType.NVarChar) { Value = userName });
        }
        public int FindListCount(string userName, bool? isYZ, int loginDJType, int OPDJTZ)
        {
            var strB = new StringBuilder();
            strB.Append(" and Name like '%'+@name+'%' ");
            if(isYZ != null)
            {
                strB.Append(" and isApprove=" + (isYZ.Value ? "1" : "0"));
            }
            if(loginDJType >= 0)
            {
                strB.Append(" and FreezeType=" + loginDJType);
            }
            if(OPDJTZ >= 0)
            {
                strB.Append(" and OpFreezeType=" + OPDJTZ);
            }

            string strWhere = " flag=1 " + strB.ToString();
            var strPage = ExpandMSSQL.GetPageSQL(
                    " T_Member ",
                    strWhere
                    );

            var intRef = MSSQLHelper.SQLConType.C1.Exp_GetSingle(strPage,
                new System.Data.SqlClient.SqlParameter("@name", SqlDbType.NVarChar) { Value = userName });
            return intRef.Exp_IntTryParse();
        }
        /// <summary>
        /// 更新基本数据
        /// </summary>
        public int UpdateBase(huaguwang.M_T_Member model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Member set ");
            strSql.Append("Name=@Name,");
            strSql.Append("Sex=@Sex,");
            strSql.Append("Birthday=@Birthday,");
            strSql.Append("Tel=@Tel,");
            strSql.Append("Email=@Email,");
            strSql.Append("IDCard=@IDCard,");
            strSql.Append("ProblemA=@ProblemA,");
            strSql.Append("ProblemB=@ProblemB,");
            strSql.Append("AnswerA=@AnswerA,");
            strSql.Append("AnswerB=@AnswerB");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@Name", SqlDbType.NVarChar,50),
					new SqlParameter("@Sex", SqlDbType.Bit,1),
					new SqlParameter("@Birthday", SqlDbType.SmallDateTime),
					new SqlParameter("@Gold", SqlDbType.Money,8),
					new SqlParameter("@Grade", SqlDbType.Int,4),
					new SqlParameter("@FreezeType", SqlDbType.TinyInt,1),
					new SqlParameter("@FreezeDate", SqlDbType.SmallDateTime),
					new SqlParameter("@Flag", SqlDbType.Bit,1),
					new SqlParameter("@PassWord", SqlDbType.NVarChar,100),
					new SqlParameter("@Tel", SqlDbType.NVarChar,50),
					new SqlParameter("@Email", SqlDbType.NVarChar,50),
					new SqlParameter("@OpFreezeType", SqlDbType.TinyInt,1),
					new SqlParameter("@OpFreezeDate", SqlDbType.SmallDateTime),
					new SqlParameter("@isApprove", SqlDbType.Bit,1),
					new SqlParameter("@IDCard", SqlDbType.NVarChar,50),
					new SqlParameter("@WinStock", SqlDbType.Decimal,5),
					new SqlParameter("@ProblemA", SqlDbType.NVarChar,200),
					new SqlParameter("@ProblemB", SqlDbType.NVarChar,200),
					new SqlParameter("@AnswerA", SqlDbType.NVarChar,200),
					new SqlParameter("@AnswerB", SqlDbType.NVarChar,200)};
            parameters[0].Value = model.id;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.Sex;
            parameters[3].Value = model.Birthday;
            parameters[4].Value = model.Gold;
            parameters[5].Value = model.Grade;
            parameters[6].Value = model.FreezeType;
            parameters[7].Value = model.FreezeDate;
            parameters[8].Value = model.Flag;
            parameters[9].Value = model.PassWord;
            parameters[10].Value = model.Tel;
            parameters[11].Value = model.Email;
            parameters[12].Value = model.OpFreezeType;
            parameters[13].Value = model.OpFreezeDate;
            parameters[14].Value = model.isApprove;
            parameters[15].Value = model.IDCard;
            parameters[16].Value = model.WinStock;
            parameters[17].Value = model.ProblemA;
            parameters[18].Value = model.ProblemB;
            parameters[19].Value = model.AnswerA;
            parameters[20].Value = model.AnswerB;

            int rows = MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strSql.ToString(), parameters);
            return rows.Exp_IsGreaterThan(0, model.id);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int UpdateFreeze(int id, int FreezeType, DateTime FreezeDate, int OpFreezeType, DateTime OpFreezeDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Member set ");
            strSql.Append("FreezeType=@FreezeType,");
            strSql.Append("FreezeDate=@FreezeDate,");
            strSql.Append("OpFreezeType=@OpFreezeType,");
            strSql.Append("OpFreezeDate=@OpFreezeDate");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@FreezeType", SqlDbType.TinyInt,1),
					new SqlParameter("@FreezeDate", SqlDbType.SmallDateTime),
					new SqlParameter("@OpFreezeType", SqlDbType.TinyInt,1),
					new SqlParameter("@OpFreezeDate", SqlDbType.SmallDateTime),};
            parameters[0].Value = id;
            parameters[1].Value = FreezeType;
            parameters[2].Value = FreezeDate;
            parameters[3].Value = OpFreezeType;
            parameters[4].Value = OpFreezeDate;

            int rows = MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strSql.ToString(), parameters);
            if(rows > 0)
            {
                return id;
            }
            else
            {
                return 0;
            }


        }

    }
}
